package com.brother.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.brother.database.DataBase;
import com.brother.model.RealTimeData;
import com.brother.model.Signal;

public class RecoverRealtimeDataFromReport {

private static boolean isRunning = false;	
	
/**从report中插入数据到realtimedata中
 * @throws SQLException
 */
public static void recover() throws SQLException{
	if(isRunning){
		Logger.getLogger(RecoverRealtimeDataFromReport.class).warn(" The recovery program is running.");
		return;
	}
	
	Connection conn = DataBase.getConnection();//DriverManager.getConnection("jdbc:mysql://www.smart-tracking.com:9999/gpsMap?characterEncoding=utf-8", "cong", "29749716");
	try{
		isRunning = true;
		Statement stmt = conn.createStatement();
		String sql = "select trackerid,trackername,usertrackerid from tracker where trackerid not in (select distinct trackerid from realtimedata)";
		ResultSet rs = stmt.executeQuery(sql);
		List<String> trackers = new ArrayList<String>();
		List<String> imeis = new ArrayList<String>();
		while(rs.next()){
			System.out.println(rs.getString("trackerid"));
			trackers.add(rs.getString("trackerid"));
			imeis.add(rs.getString("usertrackerid"));
		}
		for(int i=0;i<trackers.size(); i++){
			String trackerId = trackers.get(i);
			String imei = imeis.get(i);
			sql = "select * from r_"+imei+" order by createTime desc limit 0,1";
			System.out.println("max sql="+sql);
			try{
			rs= stmt.executeQuery(sql);
			}catch(Exception e){
				System.err.println(e.getMessage());
				continue;
			}
	//		sql = "select * from report where reportid="+rs.getString("reportid");
	//		System.out.println("data sql="+sql);
	//		rs= stmt.executeQuery(sql);
			if(rs.next()){
			System.out.println(rs.getString("address")+" "+rs.getString("lat")+"  "+rs.getString("lng")
					+rs.getString("latorient")+"  "+rs.getString("lngorient")+"  "+rs.getTimestamp("createtime"));
			
			
				final RealTimeData data = new RealTimeData();
				data.setOrient("0");
				data.setOrig_lat(rs.getString("orig_lat"));
				data.setLatOrient(rs.getString("latorient"));
				data.setLat(rs.getFloat("lat"));
				
				data.setOrig_lng(rs.getString("orig_lng"));
				data.setLng(rs.getFloat("lng"));
				data.setLngOrient(rs.getString("lngorient"));
				
				data.setTrackerid(trackerId);
				data.setState(rs.getString("state"));
				data.setSignal(Signal.ok.name());
				data.setSpeed(rs.getFloat("speed"));
				
				data.setDistance("0");
				data.setTraceDateTime(rs.getTimestamp("createtime"));
				data.setLanguage(rs.getString("language"));
				data.setAddress(rs.getString("address"));
			    
				StringBuffer sb = new StringBuffer();
				sb.append("insert into realtimedata(trackerid,tracetime,address,orig_lat,latorient," +
						"lngorient,orig_lng,lat,lng,speed,signal,orient,distance,state,language) values(");
				sb.append("'"+data.getTrackerid()+"'");
				sb.append(",'"+data.getTraceDateTime()+"'");
				sb.append(",'"+(data.getAddress() != null ? data.getAddress().replaceAll("'", "\\\\'"):"")+"'");
				sb.append(",'"+data.getOrig_lat()+"'");
				sb.append(",'"+data.getLatOrient()+"'");
				sb.append(",'"+data.getLngOrient()+"'");
				sb.append(",'"+data.getOrig_lng()+"'");
				sb.append(","+data.getLat()+"");
				sb.append(","+data.getLng()+"");
				sb.append(","+data.getSpeed()+"");
				sb.append(",'na'");
				sb.append(",'0'");
				sb.append(",0");
				sb.append(",''");
				sb.append(",'"+data.getLanguage()+"')");
			    System.out.println("insert "+sb.toString());
				stmt.executeUpdate(sb.toString());
				sb.delete(0, sb.length());
			}
			rs.close();
		}
	}catch(SQLException ex){
		Logger.getLogger(RecoverRealtimeDataFromReport.class).error("", ex);
	}finally{
		isRunning = false;
		conn.close();
	}

}	
	
}
